USE [Musicman]
GO

if  not exists (select top 10 * from musicman.information_schema.tables where table_catalog = 'Musicman'
	and table_schema = 'dbo' and table_name = 'UserRoles')
begin 

CREATE TABLE [dbo].[UserRoles](
	[UserID] [bigint] NOT NULL,
	[Role] [varchar](20) COLLATE Cyrillic_General_CI_AS NOT NULL,
 CONSTRAINT [PK_UserRoles_1] PRIMARY KEY CLUSTERED 
(
	[UserID] ASC,
	[Role] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

end 
GO

if not exists(select * from information_schema.CONSTRAINT_COLUMN_USAGE where constraint_name = 'FK_UserRoles_Users')
begin 
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
ALTER TABLE dbo.UserRoles ADD CONSTRAINT
	FK_UserRoles_Users FOREIGN KEY
	(
	UserID
	) REFERENCES dbo.Users
	(
	Personid
	) ON UPDATE  NO ACTION 
	 ON DELETE  NO ACTION 
	
COMMIT
end 
go
